package com.roc.web.action.agent;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;
import com.roc.util.Constance;
import com.roc.util.constant.Role;

public class AgentStatisticsSearchCondition extends BaseSearchCondition {

	
	private String 	minDate; // 交易开始时间(年月日)
	private String 	maxDate; // 交易结束时间(年月日)
	private String 	areaCode; // 区域id
	private String 	sonCode; // 一级代理商id

	private String orgCode;// 机构号

	private Object [] obj;//代理商id

	private String op ;// 登陆账号id
	private String v_isQueryArea ;// 是否出现区域信息，1，查询区域信息，2，查询一级代理商信息
	
	private String roleType; // 角色类别
	
	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getOrderPartOfHQL()
	 */
	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		
		return " ";
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getRowCountHQL()
	 */
	@Override
	protected String getRowCountHQL() {// 48502000   85278888
		StringBuffer sb=new StringBuffer();
		sb.append( "SELECT count(1) " );
		sb.append( "FROM ( " );

		String str = "";
		// 查询区域
	/*	if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("1")){
		  	str = "tmp.AREAID,";
		}*/
		// 查询一级代理商
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("2")){
		  	str = "tmp.PID,";
		}
		
		sb.append( " SELECT  " + str + " COUNT(tmp1.status) count, tmp1.status , min( tmp.AREANAME ) AREANAME ,min( tmp.PNAME ) PNAME" );
		sb.append( "   FROM tbl_edit_mcht_inf tmp1 " );
		
		// 
		sb.append( "  , ( " );
		sb.append( getSqlStr() );

		sb.append( " ) tmp  "  );
		sb.append( " where 1 = 1   "  );
		/*
		if( null != areaCode && !"".equals(areaCode) && !"-1".equals(areaCode) ){
			sb.append( " and tmp.AREAID = '" + areaCode + "' "  ); 
		}*/
		if( null != sonCode && !"".equals(sonCode) && !"-1".equals(sonCode) ){
			sb.append( " and tmp.PID = '" + sonCode + "' "  );
		}
		sb.append( "    AND tmp.tid = tmp1.ext1 ");
		
		sb.append( "    AND tmp1.bank_belong_cd = '" + orgCode + "' " );
		sb.append( "    AND tmp1.ext1 IS NOT NULL " );

		if (Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) {
			String startDate = minDate.replace("-", "");
			String endDate = maxDate.replace("-", "");

	    	sb.append( "   AND TO_CHAR(tmp1.rec_crt_ts, 'yyyyMMdd') >= '" + startDate + "'" );
	    	sb.append( "   AND TO_CHAR(tmp1.rec_crt_ts, 'yyyyMMdd') <= '" + endDate + "'" );
		}
	/*	// 查询区域
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("1")){

		  	sb.append( " GROUP BY tmp.AREAID,tmp1.status " );  
		  	sb.append( " ORDER BY tmp.AREAID ASC " );
		}*/
		// 查询一级代理商
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("2")){

		  	sb.append( " GROUP BY tmp.PID,tmp1.status " );  
		  	sb.append( " ORDER BY min( tmp.AREAID ) ,tmp.PID ASC " );
		}
  		sb.append( "  ) t  " );

		return sb.toString();
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getSearchHQL()
	 */
	@Override
	protected String getSearchHQL() {
		StringBuffer sb=new StringBuffer();

		String str = "";
	/*	// 查询区域
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("1")){
		  	str = "tmp.AREAID,";
		}*/
		// 查询一级代理商
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("2")){
		  	str = "tmp.PID,";
		}
		
		sb.append( " SELECT  " + str + " COUNT(tmp1.status) count, tmp1.status , min( tmp.AREANAME ) AREANAME ,min( tmp.PNAME ) PNAME" );
		sb.append( "   FROM tbl_edit_mcht_inf tmp1 " );
		
		// 
		sb.append( "  , ( " );
		sb.append( getSqlStr() );

		sb.append( " ) tmp  "  );
		sb.append( " where 1 = 1   "  );
		/*
		if( null != areaCode && !"".equals(areaCode) && !"-1".equals(areaCode) ){
			sb.append( " and tmp.AREAID = '" + areaCode + "' "  ); 
		}*/
		if( null != sonCode && !"".equals(sonCode) && !"-1".equals(sonCode) ){
			sb.append( " and tmp.PID = '" + sonCode + "' "  );
		}
		sb.append( "    AND tmp.tid = tmp1.ext1 ");
		
		sb.append( "    AND tmp1.bank_belong_cd = '" + orgCode + "' " );
		sb.append( "    AND tmp1.ext1 IS NOT NULL " );

		if (Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) {
			String startDate = minDate.replace("-", "");
			String endDate = maxDate.replace("-", "");

	    	sb.append( "   AND TO_CHAR(tmp1.rec_crt_ts, 'yyyyMMdd') >= '" + startDate + "'" );
	    	sb.append( "   AND TO_CHAR(tmp1.rec_crt_ts, 'yyyyMMdd') <= '" + endDate + "'" );
		}/*
		// 查询区域
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("1")){

		  	sb.append( " GROUP BY tmp.AREAID,tmp1.status " );  
		  	sb.append( " ORDER BY tmp.AREAID ASC " );
		}*/
		// 查询一级代理商
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("2")){

		  	sb.append( " GROUP BY tmp.PID,tmp1.status " );  
		  	sb.append( " ORDER BY min( tmp.AREAID ) ,tmp.PID ASC " );
		}
	  	
		return sb.toString();
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getWhereHQLFieldBeanList()
	 */
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
/*		
		if (Constance.isNotEmpty(minDate) && Constance.isNotEmpty(maxDate)) {
			//Date startDate = Constance.formateDate(minDate);
			//Date endDate = Constance.formateDate(maxDate);
			String startDate = minDate.replace("-", "");
			String endDate = maxDate.replace("-", "");
			filedBeanList.add(new WhereHQLFieldBean("to_char(s.DT_CREATE,'yyyyMMdd')", startDate,
					">=", false));
			filedBeanList.add(new WhereHQLFieldBean("to_char(s.DT_CREATE,'yyyyMMdd')", endDate,
					"<=", false));
		}*/
	//	if(obj != null){
	//		filedBeanList.add(new WhereHQLFieldBean("o.id", obj, "IN", false));
	//	}
/*		if(agentIdList!= null && agentIdList.length > 0){//下级代理商
        	filedBeanList.add(new WhereHQLFieldBean("s.ID IN("+Constance.getArrayContent(agentIdList)+")"));
        }*/
		return filedBeanList;
	}

	public String getSqlStr (){
		StringBuffer sql = new StringBuffer();
		sql.append( " select tt.TID TID, "  );
		sql.append( "         tt.real_name, "  );
		sql.append( "          "  );/*
		sql.append( "         subStr(tt.path, "  );
		sql.append( "                (INSTR(tt.path, '\\', 1, 2) + 1), "  );
		sql.append( "                (CASE "  );
		sql.append( "                  WHEN tt.v_level = 2 THEN "  );
		sql.append( "                   length(tt.path) "  );
		sql.append( "                  ELSE "  );
		sql.append( "                   (INSTR(tt.path, '\\', 1, 3) - INSTR(tt.path, '\\', 1, 2) - 1) "  );
		sql.append( "                END)) AREAID, "  );
		sql.append( "         subStr(tt.name, "  );
		sql.append( "                (INSTR(tt.name, '\\', 1, 2) + 1), "  );
		sql.append( "                (CASE "  );
		sql.append( "                  WHEN tt.v_level = 2 THEN "  );
		sql.append( "                   length(tt.name) "  );
		sql.append( "                  ELSE "  );
		sql.append( "                   (INSTR(tt.name, '\\', 1, 3) - INSTR(tt.name, '\\', 1, 2) - 1) "  );
		sql.append( "                END)) AREANAME, "  );
		sql.append( "         (CASE "  );
		sql.append( "           WHEN tt.v_level = 2 THEN "  );
		sql.append( "            '' "  );
		sql.append( "           ELSE "  );
		sql.append( "            subStr(tt.path,(INSTR(tt.path,'\\', 1, 3) + 1),(CASE "  );
		sql.append( "           WHEN tt.v_level = 3 THEN "  );
		sql.append( "            length(tt.path) "  );
		sql.append( "           ELSE "  );
		sql.append( "            (INSTR(tt.path, '\\', 1, 4) - INSTR(tt.path, '\\', 1, 3) - 1) "  );
		sql.append( "         END)) END) PID, "  );
		sql.append( "         (CASE "  );
		sql.append( "           WHEN tt.v_level = 2 THEN "  );
		sql.append( "            '' "  );
		sql.append( "           ELSE "  );
		sql.append( "            subStr(tt.name,(INSTR(tt.name,'\\', 1, 3) + 1),(CASE "  );
		sql.append( "           WHEN tt.v_level = 3 THEN "  );
		sql.append( "            length(tt.name) "  );
		sql.append( "           ELSE "  );
		sql.append( "            (INSTR(tt.name, '\\', 1, 4) - INSTR(tt.name, '\\', 1, 3) - 1) "  );
		sql.append( "         END)) END) PNAME "  );*/
		
		sql.append( "                tt.AREAID, " );
		sql.append( "                tt.AREANAME, " );
		sql.append( "                (CASE " );
		sql.append( "                  WHEN tt.v_level = 2 THEN " );
		sql.append( "                   subStr(tt.path, " );
		sql.append( "                          (INSTR(tt.path, '\\', 1, 2) + 1), " );
		sql.append( "                          length(tt.path)) " );
		sql.append( "                  ELSE " );
		sql.append( "                   subStr(tt.path, " );
		sql.append( "                          (INSTR(tt.path, '\\', 1, 2) + 1), " );
		sql.append( "                          (INSTR(tt.path, '\\', 1, 3) - " );
		sql.append( "                          INSTR(tt.path, '\\', 1, 2) - 1)) " );
		sql.append( "                END) PID, " );
		sql.append( "                (CASE " );
		sql.append( "                  WHEN tt.v_level = 2 THEN " );
		sql.append( "                   subStr(tt.name, " );
		sql.append( "                          (INSTR(tt.name, '\\', 1, 2) + 1), " );
		sql.append( "                          length(tt.name)) " );
		sql.append( "                  ELSE " );
		sql.append( "                   subStr(tt.name, " );
		sql.append( "                          (INSTR(tt.name, '\\', 1, 2) + 1), " );
		sql.append( "                          (INSTR(tt.name, '\\', 1, 3) - " );
		sql.append( "                          INSTR(tt.name, '\\', 1, 2) - 1)) " );
		sql.append( "                END) PNAME " );

		sql.append( "   "  );
		sql.append( "    from (SELECT TO_CHAR(T1.ID) TID, "  );
		sql.append( "                 t1.real_name, "  );
		sql.append( "                 TO_CHAR(SYS_CONNECT_BY_PATH(t1.id, '\\')) AS path, "  );
		sql.append( "                 TO_CHAR(SYS_CONNECT_BY_PATH(t1.real_name, '\\')) name, "  );
		sql.append( "                 t1.companyid AREAID, " );
		sql.append( "                 (select A1.Name " );
		sql.append( "                      from tbl_area A1 " );
		sql.append( "                      where A1.Ins_Code = T1.org_code " );
		sql.append( "                            and A1.id = T1.companyid) AREANAME, " );
		sql.append( "                 LEVEL v_level "  );
		sql.append( "            FROM T_OPERATORS T1 "  );
		sql.append( "           START WITH t1.ID = '" + op + "' "  );
	/*	// 如果不是 系统管理员
		if ( !roleType.equals(Role.SYSTEM.getCode()) ){
			sql.append( "                  AND T1.ROLE_TYPE = '03' "  );
		}*/
		sql.append( "          CONNECT BY PRIOR T1.ID = T1.SUPRI_ID "  );
		// 如果是 系统管理员
	//	if ( roleType.equals(Role.SYSTEM.getCode()) ){
			sql.append( "                  AND T1.ROLE_TYPE in( '02', '03') "  );
	//	}
		sql.append( "          ) tt "  );
		sql.append( "   where 1 = 1  "  );
		/*
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("1")){
			sql.append( " and  v_level = 2 "  );
		}*/
		if( v_isQueryArea != null && !v_isQueryArea.equals("") && v_isQueryArea.equals("2")){
			sql.append( " and v_level > 1 "  );
		}
		return sql.toString();
	}
	
	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}

	public String getOrgCode() {
		return orgCode;
	}

	public String getAreaCode() {
		return areaCode;
	}

	public void setAreaCode(String areaCode) {
		this.areaCode = areaCode;
	}

	public String getSonCode() {
		return sonCode;
	}

	public void setSonCode(String sonCode) {
		this.sonCode = sonCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}
	
	public Object[] getObj() {
		return obj;
	}

	public void setObj(Object[] obj) {
		this.obj = obj;
	}

	public String getOp() {
		return op;
	}

	public void setOp(String op) {
		this.op = op;
	}

	public String getV_isQueryArea() {
		return v_isQueryArea;
	}

	public void setV_isQueryArea(String v_isQueryArea) {
		this.v_isQueryArea = v_isQueryArea;
	}

	public String getRoleType() {
		return roleType;
	}

	public void setRoleType(String roleType) {
		this.roleType = roleType;
	}

}
